﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Windows.Forms;

namespace ACCPS1ProjectDemo
{
    public class DBHelper
    {
        public  string connString = "Data Source=.;Initial Catalog=MySchool;Persist Security Info=True;User ID=sa;Password=sa";
        /// <summary>
        ///执行查询单个值的select语句
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <returns></returns>
        public object ExecuteScalar(string sql)
        {
            //创建链接
            SqlConnection Conn = new SqlConnection(connString);
            //创建命令执行对象
            SqlCommand Cmd = new SqlCommand(sql, Conn);
            object jieGuo = 0;
            try
            {
                Conn.Open();//打开链接
                //执行命令
                jieGuo = (int)Cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //关闭链接
                Conn.Close();
            }
            return jieGuo;
        }

        /// <summary>
        /// 执行Select语句，如果查询结果是个单值，推荐使用 ExecuteScalar方法
        /// </summary>
        /// <param name="sql">要执行的sql</param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string sql)
        {
            //创建链接
            SqlConnection Conn = new SqlConnection(connString);
            //创建命令执行对象
            SqlCommand Cmd = new SqlCommand(sql, Conn);
            SqlDataReader reader = null;
            try
            {
                Conn.Open();//打开链接
                //执行命令
                reader = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            
            return reader;
        }


        /// <summary>
        /// 执行insert、update、delete语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>受影响行数</returns>
        public int ExecutNonQuery(string sql)
        {
            //创建链接
            SqlConnection Conn = new SqlConnection(connString);
            //创建命令执行对象
            SqlCommand Cmd = new SqlCommand(sql, Conn);
            int jieGuo = 0;
            try
            {
                Conn.Open();//打开链接
                //执行命令
                jieGuo = Cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //关闭链接
                Conn.Close();
            }
            return jieGuo;
        }


        public DataTable ExectueTable(string sql)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connString);
            DataSet ds = new DataSet();
            try
            {
                adapter.Fill(ds);
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return ds.Tables[0];
        }



    }
}
